"""
SqlAlchermy 外键及其四种约束讲解
1. RESTRICT：父表数据被删除，会阻止删除。默认就是这一项
2. NO ACTION：在MySQL中，同RESTRICT
3. CASCADE：级联删除
4. SET NULL：父表数据被删除，子表数据会设置为NULL
"""

import os
import sys

path = os.path.dirname(__file__)
sys.path.append(os.path.join(path, '..'))

from faker import Faker
from sqlalchemy import Column, Integer, String, ForeignKey
from conf.mysql_config import BASE, SESSION


class Company(BASE):
    __tablename__ = 'company'
    id = Column(Integer, autoincrement=True, primary_key=True)
    company_name = Column(String(200), comment='公司名', doc={'zh': '公司名'})

    def __str__(self):
        return f'<Company id={self.id}, company_name={self.company_name}>'

    __repr__ = __str__


class Job(BASE):
    __tablename__ = 'company_job'
    id = Column(Integer, autoincrement=True, primary_key=True)
    job_name = Column(String(200), comment='职位', doc={'zh': '职位'})
    # company_id = Column(Integer, ForeignKey('company.id'))  # 1. RESTRICT，默认就是这一项
    # company_id = Column(Integer, ForeignKey('company.id', ondelete='NO ACTION'))  # 2. NO ACTION，mysql中行为同RESTRICT一致
    # company_id = Column(Integer, ForeignKey('company.id', ondelete='CASCADE'))  # 3. CASCADE
    company_id = Column(Integer, ForeignKey('company.id', ondelete='SET NULL'))  # 4. SET NULL

    def __str__(self):
        return f'<Job id={self.id}, job_name={self.job_name}>'

    __repr__ = __str__


BASE.metadata.drop_all()
BASE.metadata.create_all()


fake = Faker(locale='zh_CN')


def add_company_data():
    company = Company(company_name=fake.company())
    SESSION.add(company)
    SESSION.commit()


def add_company_job_data():
    company = SESSION.query(Company).first()
    jobs = [Job(job_name=fake.job(), company_id=company.id) for _ in range(10)]
    SESSION.add_all(jobs)
    SESSION.commit()


def delete_company():
    try:
        company = SESSION.query(Company).first()
        SESSION.delete(company)
        SESSION.commit()
    except (Exception, ) as e:
        print(e)


if __name__ == '__main__':
    add_company_data()
    add_company_job_data()
    delete_company()
